﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spRestoreKillProcesses]
#-- Purpose:		Kills any open connections to a database that is about to
#--					be restored.
#--	Last Update:	06/01/2012
#--					For a complete history - please review comments in SVN
#-- Called By:		[dbo].[spRestoreDatabase_BackupSetID]
#--					[dbo].[spRestoreDatabase_Label]
#--					[dbo].[spRestoreDatabase_PointInTime]
#--					Restore Database Utility Executable
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spRestoreKillProcesses]
(
	@database_name	sysname
)
WITH EXECUTE AS OWNER
AS

SET NOCOUNT ON

--- Determine if the user is authorized to do restores
DECLARE	@sError varchar(1000), @has_rights bit
EXEC dbo.spRestoreIsAllowed @database_name = @database_name, @is_allowed = @has_rights OUTPUT
IF @has_rights = 0
  BEGIN
	SELECT	@sError = 'You are not authorized to restore the database ' + @database_name
	RAISERROR (@sError, 16, 1)
  END

--- @database_name is mandatory
IF LTRIM(RTRIM(ISNULL(@database_name, ''))) = ''
  BEGIN
	RAISERROR('@database_name is a mandatory parameter', 16, 1)
	RETURN
  END

--- Declare Local Variables
DECLARE		@sCommand varchar(8000)
DECLARE		@progname sysname

--- Define the name of this program (for logs)
SELECT		@progname = 'spRestoreKillProcesses'

--- Kill open connections
DECLARE crs CURSOR FOR
	SELECT	'KILL ' + spid + '     -- ' + ISNULL(loginame, 'unknown') + ' on ' + ISNULL(hostname, 'unknown') + ' at ' + last_batch + ' with ' + ISNULL(progname, 'unknown')
	FROM	(
			SELECT		CAST(spid as varchar(20)) spid,
						(CASE WHEN LTRIM(RTRIM(loginame)) = '' THEN NULL ELSE LTRIM(RTRIM(loginame)) END) loginame,
						(CASE WHEN LTRIM(RTRIM(hostname)) = '' THEN NULL ELSE LTRIM(RTRIM(hostname)) END) hostname,
						CONVERT(varchar(50), last_batch) last_batch,
						(CASE WHEN LTRIM(RTRIM(program_name)) = '' THEN NULL ELSE LTRIM(RTRIM(program_name)) END) progname
			FROM		sys.sysprocesses 
			WHERE		DB_NAME(dbid) = @database_name
						AND spid > 50
			) t1
OPEN crs
FETCH NEXT FROM crs INTO @sCommand
WHILE @@FETCH_STATUS <> -1
  BEGIN
	BEGIN TRY
		EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
		EXEC	(@sCommand)
	END TRY
	BEGIN CATCH
		PRINT 'Command failed: ' + @sCommand
	END CATCH

	FETCH NEXT FROM crs INTO @sCommand
  END
CLOSE crs
DEALLOCATE crs

SET NOCOUNT OFF
